﻿using OfficeOpenXml;
using System.Data;

namespace IMES.Utils
{
    /// <summary>
    /// Excel 处理帮助类
    /// </summary>
    public class ExcelHelper
    {
        public delegate T ExcelReadFormatFun<T>(ExcelWorksheet sheet,int row);
        /// <summary>
        /// 从Excel文件读取标准DataTable数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public DataTable ReadData(string filePath)
        {
            DataTable dt =new DataTable ();
            try
            {
                if (!File.Exists(filePath))
                    throw new Exception("文件不存在!");
                var extension = Path.GetExtension(filePath).ToUpper();
                if (!extension.Contains("XLSX"))
                    throw new Exception("文件格式不正确,只支持XLSX文件");

                Stream stream = File.OpenRead(filePath);
                using (var package = new ExcelPackage(stream))
                {
                    // 获取Exel指定工作簿，"Sheet1"也可以用索引代替
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[0];

                    //列头处理
                    int ColumnNum = worksheet.Dimension.Columns;
                    for (int col = 1; col <= ColumnNum; col++)
                    {
                        dt.Columns.Add(worksheet.Cells[1, col].Value.ToString());
                    }

                    // 获取数据行数
                    int RowNum = worksheet.Dimension.Rows;
                    // 获取每行数据
                    for (int row = 2; row <= RowNum; row++)
                    {
                        dt.Rows.Add(dt.NewRow());
                        for (int col = 1; col <= ColumnNum; col++)
                        {
                            dt.Rows[dt.Rows.Count-1][col - 1] = worksheet.Cells[row, col].Value;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// Excel数据读取为实体列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filePath">文件路径</param>
        /// <param name="format">行数据格式化泛型委托</param>
        /// <returns></returns>
        public List<T> ReadEntityList<T>(string filePath, ExcelReadFormatFun<T> format)
        {
            List<T> list = new List<T>();
            try
            {
                if (!File.Exists(filePath))
                    throw new Exception("文件不存在!");
                var extension = Path.GetExtension(filePath).ToUpper();
                if (!extension.Contains("XLSX"))
                    throw new Exception("文件格式不正确,只支持XLSX文件");

                Stream stream = File.OpenRead(filePath);
                using (var package = new ExcelPackage(stream))
                {
                    // 获取Exel指定工作簿，"Sheet1"也可以用索引代替
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[0]; 
                    // 获取数据行数
                    int RowNum = worksheet.Dimension.Rows;
                    // 获取每行数据
                    for (int row = 2; row <= RowNum; row++)
                    {
                        list.Add(format(worksheet, row));
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        /// <summary>
        /// DataTable 数据保存到Excel
        /// </summary>
        /// <param name="file"></param>
        /// <param name="dt"></param>
        public void SaveFile(FileInfo file,DataTable dt)
        { 
            using (ExcelPackage package = new ExcelPackage(file))
            {
                //工作簿
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");
                //填充表头
                for (int i = 1; i < dt.Columns.Count + 1; i++)
                {
                    worksheet.Cells[1, i].Value = dt.Columns[i - 1].ColumnName;
                }

                //填充行(从第二行开始)
                for (int i = 2; i < dt.Rows.Count + 2; i++)
                {
                    //填充行内列
                    for (int j = 1; j < dt.Columns.Count + 1; j++)
                    {
                        worksheet.Cells[i, j].Value =dt.Rows[i-2][dt.Columns[j-1].ColumnName];
                    }
                } 
                //列宽自适应
                worksheet.Cells.AutoFitColumns(); 
                //保存
                package.Save();
            }
        }

        /// <summary>
        /// 实体列表保存到Excel文件
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="file">保存的文件</param>
        /// <param name="dataList">数据列表</param>
        /// <param name="fields">字段列表</param>
        public void SaveEntityToFile<T>(FileInfo file, List<T> dataList,List<ExcelHelperSaveField> fields)
        {
            using (ExcelPackage package = new ExcelPackage(file))
            {
                //工作簿
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");
                fields=fields.OrderBy(a=>a.ShowIndex).ToList();
                //填充表头
                for (int i = 1; i < fields.Count + 1; i++)
                {
                    worksheet.Cells[1, i].Value = fields[i-1].FieldTitle;
                } 
                //填充行(从第二行开始)
                for (int i = 2; i < dataList.Count + 2; i++)
                {
                    //填充行内列
                    for (int j = 1; j < fields.Count + 1; j++)
                    {
                        worksheet.Cells[i, j].Value = dataList[i - 2].GetType().GetProperty(fields[j-1].FieldName).GetValue(dataList[i - 2]);
                    }
                }
                //列宽自适应
                worksheet.Cells.AutoFitColumns();
                //保存
                package.Save();
            }
        }

    }

    /// <summary>
    /// Excel保存字段
    /// </summary>
    public class ExcelHelperSaveField
    {
        /// <summary>
        /// 显示顺序索引
        /// </summary>
        public int ShowIndex { get; set;}
        /// <summary>
        /// 字段名称
        /// </summary>
        public string FieldName { get; set; }
        /// <summary>
        /// 字段显示文字
        /// </summary>
        public string FieldTitle { get; set; }
    }
}
